﻿using System;
using System.Collections.ObjectModel;
using System.Data.SqlClient;
using System.Globalization;
using System.Linq;
using System.Text;
using MySql.Data.MySqlClient;

namespace Reviewer.Data.Bases
{
    [CLSCompliant(false)]
    public abstract class RepositoryCrudBase<T, TPrimaryKey> : RepositoryBase<T>
        where T : class
        where TPrimaryKey : struct
    {
        #region methods (public)
        /// <summary>
        /// Creates the specified item.
        /// </summary>
        /// <param name="item">The item.</param>
        /// <returns></returns>
        public virtual T Create(T item)
        {
            StringBuilder query = new StringBuilder();
            query.AppendFormat("INSERT INTO {0} ", this.TableName);
            StringBuilder queryNames = new StringBuilder();
            StringBuilder queryParams = new StringBuilder();
            CrudColumnInfo[] columns = this.Columns;
            bool isFirst = true;
            foreach (CrudColumnInfo column in columns.Where(co => co.IsPrimaryKey ? co.AutoIncrement ? false : true : true))
            {
                if (isFirst)
                {
                    isFirst = false;
                }
                else
                {
                    queryNames.Append(",");
                    queryParams.Append(",");
                }
                queryNames.Append(column.Name);
                queryParams.AppendFormat("@{0}", column.Name);
            }
            query.AppendFormat(" ({0}) VALUES ({1})", queryNames.ToString(), queryParams.ToString());
            using (MySqlCommand command = new MySqlCommand(query.ToString()))
            {
                foreach (CrudColumnInfo column in columns.Where(co => co.IsPrimaryKey ? co.AutoIncrement ? false : true : true))
                {
                    column.AddParameter(command, this.ColumnToValue(column, item));
                }

                ExecuteNonQuery(command);

                CrudColumnInfo primary = columns.Single(co => co.IsPrimaryKey);
                if (primary != null && primary.AutoIncrement)
                {

                    TPrimaryKey primaryKeyValue = (TPrimaryKey)this.ConvertTo(typeof(TPrimaryKey), command.LastInsertedId);
                    return this.SelectBy(primaryKeyValue);
                }
                else if (primary != null)
                {
                    return this.SelectBy((TPrimaryKey)this.ColumnToValue(primary, item));
                }
                return null;
            }
        }

        /// <summary>
        /// Selects item by the key.
        /// </summary>
        /// <param name="key">The key.</param>
        /// <returns></returns>
        public virtual T SelectBy(TPrimaryKey key)
        {
            CrudColumnInfo keyColumn = this.Columns.Single(co => co.IsPrimaryKey);

            using (MySqlCommand command = new MySqlCommand(
                String.Format(CultureInfo.InvariantCulture, "{0} WHERE {1}=@{1}"
                , this.SelectAllQuery()
                , keyColumn.Name)))
            {
                keyColumn.AddParameter(command, key);
                return ExecuteReaderAndBuildItem(command);
            }
        }

        /// <summary>
        /// Selects all.
        /// </summary>
        /// <returns></returns>
        public virtual Collection<T> SelectAll()
        {
            return SelectAll(0, null, SortOrder.Unspecified);
        }

        /// <summary>
        /// Updates the specified item.
        /// </summary>
        /// <param name="item">The item.</param>
        /// <returns></returns>
        public virtual T Update(T item)
        {
            CrudColumnInfo primary = this.Columns.Single(co => co.IsPrimaryKey);

            StringBuilder query = new StringBuilder();
            query.Append("UPDATE ");
            query.Append(this.TableName);
            query.Append(" SET ");

            bool isFirst = true;
            foreach (CrudColumnInfo column in this.Columns.Where(co => co.IsUpdatable))
            {
                if (!isFirst)
                {
                    query.Append(",");
                }
                else
                {
                    isFirst = false;
                }
                query.AppendFormat("{0}=@{0}", column.Name);
            }

            query.AppendFormat(" WHERE {0}=@{0}", primary.Name);

            using (MySqlCommand command = new MySqlCommand(query.ToString()))
            {
                foreach (CrudColumnInfo column in this.Columns.Where(co => co.IsUpdatable))
                {
                    column.AddParameter(command, this.ColumnToValue(column, item));
                }
                primary.AddParameter(command, this.ColumnToValue(primary, item));

                ExecuteNonQuery(command);
            }

            return SelectBy((TPrimaryKey)this.ColumnToValue(primary, item));
        }

        /// <summary>
        /// Deletes an item by the specified key.
        /// </summary>
        /// <param name="key">The key.</param>
        public void Delete(TPrimaryKey key)
        {
            CrudColumnInfo keyColumn = this.Columns.Single(co => co.IsPrimaryKey);

            using (MySqlCommand command = new MySqlCommand(
                String.Format(CultureInfo.InvariantCulture, "DELETE FROM {0} WHERE {1}=@{1}"
                , this.TableName
                , keyColumn.Name)))
            {
                keyColumn.AddParameter(command, key);
                ExecuteNonQuery(command);
            }
        }
        #endregion

        #region methods (protected)
        internal Collection<T> SelectAll(int count, CrudColumnInfo orderByColumn, SortOrder order)
        {
            using (MySqlCommand command = new MySqlCommand(this.SelectAllQuery()))
            {
                if (orderByColumn != null)
                {
                    command.CommandText += " ORDER BY " + orderByColumn.Name;
                    switch (order)
                    {
                        case SortOrder.Ascending:
                            command.CommandText += " ASC";
                            break;
                        case SortOrder.Descending:
                            command.CommandText += " DESC";
                            break;
                    }
                }

                if (count > 0)
                {
                    command.CommandText += " LIMIT " + count.ToString(CultureInfo.InvariantCulture);
                }

                return ExecuteReaderAndBuildList(command);
            }
        }

        protected virtual object ConvertTo(Type toType, object value)
        {
            if (toType.Equals(typeof(System.Int32)))
            {
                return Convert.ToInt32(value, CultureInfo.InvariantCulture);
            }
            return null;
        }

        protected virtual string SelectAllQuery()
        {
            return SelectAllQuery(String.Empty);
        }

        protected virtual string SelectAllQuery(string prefix)
        {
            StringBuilder columnNames = new StringBuilder();
            CrudColumnInfo[] columns = this.Columns;
            for (int index = 0; index < columns.Length; index++)
            {
                if (index > 0)
                {
                    columnNames.Append(",");
                }

                if (!string.IsNullOrEmpty(prefix))
                {
                    columnNames.Append(prefix + ".");
                }

                columnNames.Append(columns[index].Name);

            }

            return String.Format(CultureInfo.InvariantCulture, "SELECT {1} FROM {0}", this.TableName + (String.IsNullOrEmpty(prefix) ? String.Empty : " " + prefix), columnNames.ToString());
        }

        internal virtual CrudColumnInfo OrderByColumn()
        {
            return null;
        }

        internal abstract CrudColumnInfo[] Columns { get; }
        protected abstract string TableName { get; }
        internal abstract object ColumnToValue(CrudColumnInfo info, T item);
        #endregion
    }
}
